package cn.edu.swu.cart;

import cn.edu.swu.book.Book;
import cn.edu.swu.core.ResultSetVisitor;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

import static cn.edu.swu.core.AbstractRepo.getConnection;

public class CartDAO {

    String jdbcURL = "jdbc:mysql://localhost:3306/bookstore";
    String jdbcUsername = "root";
    String jdbcPassword = "zmj410310";

    public boolean addToCart(int userId, int productId, String name, String image, Double price, int quantity) {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        }

        // 连接数据库并执行SQL语句
        String sql = "INSERT INTO cart (userId, productId, name, image, price, quantity) VALUES (?, ?, ?, ?, ?, ?)";
        try (Connection conn = DriverManager.getConnection(jdbcURL, jdbcUsername, jdbcPassword);
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setInt(1, userId);
            pstmt.setInt(2, productId);
            pstmt.setString(3, name);
            pstmt.setString(4, image);
            pstmt.setDouble(5, price);
            pstmt.setInt(6, quantity);
            pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return true;
    }

    public Book getBookById(int productId) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        Book book = null;

        try {
            // 加载和注册 JDBC 驱动程序
            Class.forName("com.mysql.cj.jdbc.Driver");

            // 建立数据库连接
            connection = DriverManager.getConnection(jdbcURL, jdbcUsername, jdbcPassword);

            // 创建 SQL 查询
            String sql = "SELECT * FROM book WHERE id = ?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, productId);

            // 执行查询
            resultSet = preparedStatement.executeQuery();

            // 处理结果集
            if (resultSet.next()) {
                book = new Book();
                book.setId(resultSet.getInt("id"));
                book.setName(resultSet.getString("name"));
                book.setImage(resultSet.getString("image"));
                book.setPrice(resultSet.getDouble("price"));
            }
        } catch (SQLException | ClassNotFoundException e) {
            e.printStackTrace();
        } finally {
            // 关闭资源
            try {
                if (resultSet != null) resultSet.close();
                if (preparedStatement != null) preparedStatement.close();
                if (connection != null) connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        return book;
    }

    public List<Cart> getCartItems(int userId) {
        List<Cart> items = new ArrayList<>();
        String sql = "SELECT c.id, c.userId, c.productId, b.name, b.image, c.quantity, b.price " +
                "FROM cart c " +
                "INNER JOIN book b ON c.productId = b.id " +
                "WHERE c.userId = ?";

        try (Connection conn = getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setInt(1, userId);
            ResultSet rs = pstmt.executeQuery();

            while (rs.next()) {
                Cart item = new Cart(
                        rs.getInt("id"),
                        rs.getInt("userId"),
                        rs.getInt("productId"),
                        rs.getString("name"),
                        rs.getString("image"),
                        rs.getInt("quantity"),
                        rs.getDouble("price")
                );
                items.add(item);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return items;
    }

    public boolean removeFromCart(int userId, int productId) {
        String sql = "DELETE FROM cart WHERE userId = ? AND productId = ?";

        try (Connection conn = getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setInt(1, userId);
            pstmt.setInt(2, productId);
            int rowsAffected = pstmt.executeUpdate();

            return rowsAffected > 0; // 如果影响的行数大于0，则删除成功
        } catch (SQLException e) {
            e.printStackTrace();
            return false; // 发生异常则返回false
        }
    }
}